SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION [SOI].[GetFormattedRate]
    (
     @AssetIntrnlId INT, @ExportId INT
    )
RETURNS VARCHAR(50)
AS
    BEGIN
		DECLARE @return VARCHAR(50)
--DECLARE @ExportId INT = (SELECT MAX(e.ExportId) FROM FSFundModel_VALCOMM.Reporting.Export e WHERE e.FundId ='FSIC')
--DECLARE @AssetIntrnlId INT = 55

DECLARE @InsDefId INT
DECLARE  @FinType VARCHAR(10)
DECLARE @BaseRateType VARCHAR(20)
DECLARE @FixedRate DECIMAL(38,10)
DECLARE @Spread DECIMAL(38,10)
DECLARE @Pik DECIMAL(38,10)
DECLARE @PctCostDiscPurchPrc DECIMAL(38,10)





SELECT @InsDefId = id.InstDefId, @FinType = id.FinType,@BaseRateType = i.BaseRateType, @FixedRate =i.FixedRate, @Spread= i.Spread,@Pik= i.PIK, @PctCostDiscPurchPrc =a.PctCostDiscPurchPrc
		    FROM FSFundModel_VALCOMM.Reporting.Asset a
				INNER JOIN  FSFundModel_VALCOMM.Reporting.Instrument i 
				ON a.ExportId = i.ExportId AND a.InstrumentId= i.InstrumentId
				INNER JOIN  FSFundModel_VALCOMM.Reporting.InstrumentDefinition id 
				ON a.ExportId = id.ExportId AND i.InstDefId =id.InstDefId
			WHERE 
			a.AssetIntrnlId = @AssetIntrnlId
			AND a.ExportId = @ExportId
	   
IF @FinType = 'Debt' 
		BEGIN
			IF @FixedRate = 0
				BEGIN
					SET @return = 
						CASE 
							WHEN @BaseRateType LIKE 'EURIBOR%' THEN 'EURIBOR+' +cast(CAST(@Spread*10000 AS INT)AS varchar(50))
							WHEN @BaseRateType LIKE 'PRIME%' THEN 'Prime+'+cast(CAST(@Spread*10000 AS INT) AS varchar(50))
							ELSE 'L+' + cast(CAST(@Spread*10000 AS INT) AS varchar(50))
						END		
				END
			ELSE 
				BEGIN
					SET @return = CAST(CAST(@FixedRate * 100 AS DECIMAL(38,1)) AS  varchar(50)) + '%'
				END 	
		END
   ELSE 
   BEGIN
		IF @InsDefId = 8 
			BEGIN
				SET @return = CAST(CAST(@PctCostDiscPurchPrc * 100 AS DECIMAL(38,1)) AS  varchar(50)) + '%'
			END	
	END

	IF @Pik <> 0  SET @return = @return + ', ' + CAST(CAST(@Pik*100 AS DECIMAL(38,1)) AS varchar(50)) + '% PIK'

		RETURN @return 
    END

GO
EXEC sp_addextendedproperty N'VirtualFolder', N'SOI', 'SCHEMA', N'SOI', 'FUNCTION', N'GetFormattedRate', NULL, NULL
GO
